﻿using FreeSql.Internal.ObjectPool;
using GalaSoft.MvvmLight;
using GalaSoft.MvvmLight.Command;
using HandyControl.Controls;
using Microsoft.Win32;
using OfficeOpenXml;
using PropertyChanged;
using sys_Print.DataAsset;
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.Common;
using System.IO;
using System.Linq;
using System.Text;
using ZXing.QrCode.Internal;
using static FreeSql.Internal.GlobalFilter;

namespace sys_Print.ViewModel
{
    [AddINotifyPropertyChangedInterface]
    public class ENNCodeViewModel : ViewModelBase
    {
        [DoNotNotify]
        private readonly IFreeSql _fsql;

        public List<string> TypeName { get; set; }
        public string SelectedTypeName { get; set; }
        public bool IsDelete { get; set; }
        public bool IsBinding { get; set; }
        public ObservableCollection<ENNCode> ENNCodes { get; set; }
        public ObservableCollection<ENNCode> ExcelData { get; set; }
        public RelayCommand UploadExcelCommand { get; private set; }
        public RelayCommand SelectCommand { get; private set; }

        public ENNCodeViewModel() 
        {
            // 获取配置好的FreeSql实例
            _fsql = App.FsqlDB;

            TypeName = _fsql.Select<ENNCode>().Distinct().ToList(x=>x.TypeName);

            UploadExcelCommand = new RelayCommand(new Action(CommandUploadExcel));
            SelectCommand = new RelayCommand(new Action(CommandSelect));
        }

        /// <summary>
        /// 查询追溯码
        /// </summary>
        private void CommandSelect()
        {
            List<ENNCode> list = _fsql.Select<ENNCode>()
                                .Where(x => x.TypeName == SelectedTypeName)
                                .WhereIf(!IsBinding, x => x.Delete == IsDelete)
                                .WhereIf(IsBinding, x => x.FactoryNo != null)
                                .ToList();

            ENNCodes = new ObservableCollection<ENNCode>(list);
        }

        /// <summary>
        /// 上传Excel
        /// </summary>
        private void CommandUploadExcel()
        {
            OpenFileDialog openFileDialog = new OpenFileDialog();
            openFileDialog.Filter = "Excel Files|*.xls;*.xlsx";
            if (openFileDialog.ShowDialog() == true)
            {
                string excelFilePath = openFileDialog.FileName;

                // 从Excel文件中读取数据
                // 使用EPPlus，来实现这一部分的逻辑
                // 返回一个包含Excel数据的DataTable
                using (var package = new ExcelPackage(new FileInfo(excelFilePath)))
                {
                    ExcelWorksheet worksheet = package.Workbook.Worksheets[0]; // 选择第一个工作表

                    int totalRows = worksheet.Dimension.Rows;
                    int totalCols = worksheet.Dimension.Columns;

                    // 添加数据
                    for (int row = 2; row <= totalRows; row++)
                    {
                        ENNCode ENNCode = new ENNCode()
                        {
                            QRCode = worksheet.Cells[row, 1].Text,
                            ObjectNumber = worksheet.Cells[row, 2].Text,
                            Number = worksheet.Cells[row, 3].Text,
                            CorporateName = worksheet.Cells[row, 4].Text,
                            TypeName = worksheet.Cells[row, 5].Text,
                        };

                        ExcelData.Add(ENNCode);
                    }
                }

                ImportDataToSqlServer(ExcelData);
            }
        }

        private void ImportDataToSqlServer(ObservableCollection<ENNCode> data)
        {
            using (Object<DbConnection> conn = _fsql.Ado.MasterPool.Get())
            {
                using (DbTransaction transaction = conn.Value.BeginTransaction())
                {
                    try
                    {
                        _fsql.Insert(data).ExecuteAffrows();

                        transaction.Commit();
                        HandyControl.Controls.Growl.Info("上传完成！");
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        //记录日志，抛出异常（使用全局异常）或者返回自定义数据 
                        throw ex;
                    }
                }
            }
        }

    }
}
